# Query schema, table, column and primary key
class MetaService:
    def __init__(self, conn):
        self.conn = conn

    def list_schemas(self):
        with self.conn.cursor() as cur:
            cur.execute("SELECT schema_name FROM information_schema.schemata "
                      "WHERE schema_name NOT LIKE 'pg_%' "
                      "AND schema_name != 'information_schema'")
            return [row[0] for row in cur.fetchall()]

    def list_tables(self, schema: str):
        with self.conn.cursor() as cur:
            cur.execute("SELECT table_name FROM information_schema.tables "
                      "WHERE table_schema=%s ORDER BY table_name", (schema,))
            return [row[0] for row in cur.fetchall()]
    
    def _safe_execute(self, sql, params=None):
        """Execute SQL, rollback if there is an error, and throw the exception again."""
        try:
            with self.conn.cursor() as cur:
                cur.execute(sql, params or ())
                return cur.fetchall()
        except Exception:
            self.conn.rollback()
            raise

    def get_columns(self, table: str):
        schema, tbl = table.replace('"', '').split('.')
        return self._safe_execute("""
            SELECT column_name, data_type,
                   CASE WHEN column_default LIKE 'nextval%%' THEN 1 ELSE 0 END AS is_serial
            FROM information_schema.columns
            WHERE table_schema=%s AND table_name=%s
            ORDER BY ordinal_position
        """, (schema, tbl))

    def get_primary_keys(self, table: str):
        schema, tbl = table.replace('"', '').split('.')
        return [row[0] for row in self._safe_execute("""
            SELECT kcu.column_name
            FROM information_schema.table_constraints tc
            JOIN information_schema.key_column_usage kcu
              ON tc.constraint_name = kcu.constraint_name
             AND tc.table_schema = kcu.table_schema
            WHERE tc.constraint_type = 'PRIMARY KEY'
              AND tc.table_schema=%s AND tc.table_name=%s
        """, (schema, tbl))]